import hashlib
import itertools
import logging
import json
import time
from functools import reduce
from operator import or_

from flask import current_app as app, url_for, request_started
from flask_login import current_user, AnonymousUserMixin, UserMixin
from passlib.apps import custom_app_context as pwd_context
from sqlalchemy.exc import DBAPIError
from sqlalchemy.dialects import postgresql

from sqlalchemy_utils import EmailType
from sqlalchemy_utils.models import generic_repr

from redash import redis_connection
from redash.utils import generate_token, utcnow

from .base import db, Column, GFKBase, key_type, primary_key
from .mixins import TimestampMixin, BelongsToOrgMixin
from .types import json_cast_property, MutableDict, MutableList

logger = logging.getLogger(__name__)

@generic_repr("id", "bill_enum", "name")
class Workflow(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="workflow")
    userid = Column(db.Integer) #--操作员ID
    name = Column(db.String(255), default=None, nullable=True) #名称
    orgunitid = Column(db.Integer, default=0, nullable=True) #--部门ID
    bill_enum = Column(db.String(255), default=None, nullable=True)
    bill_type = Column(db.Integer, default=0) #--类型ID
    is_active = Column(db.Boolean, default=False, nullable=True)

    __tablename__ = "workflow"
    __table_args__ = (db.Index("workflow_org_bill_enum_name_bill_type_orgunitid","org_id","bill_enum", "name","bill_type", "orgunitid", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.bill_enum,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "name": self.name,
            "orgunitid": self.orgunitid,
            "bill_enum": self.bill_enum,
            "is_active": self.is_active,
            "bill_type": self.bill_type,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('workflow_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
#############---表ID
    @classmethod
    def getworkflowmessage(cls,_bill,_billid,_typeid,_orgid):
        if _typeid ==0 :
            strsql="""select COALESCE(string_agg(('<p><strong><span style="color:red">' || to_char(t1.created_at, 'YYYY-MM-DD HH24:MI') || ' '
            || t2.name || case when t1.status=1 then ' 驳回意见:' else ' 审批意见:' end || '</span></strong></p>' || t1.comments),''  order by t1.created_at),'') as comments
                from public.workflow_action t1 inner join public.users t2 on t1.completed_by=t2.id
                where t1.billid=:billid and t1.bill_enum=:bill and t1.bill_type=:typeid and t1.org_id=:orgid """
            return db.session.execute(strsql,{"bill": _bill,"billid": _billid,"typeid": _typeid,"orgid": _orgid}).fetchone()
        else :
            strsql="""select public.contractshenqian(:bill,:billid,:typeid,:orgid)"""
            return db.session.execute(strsql,{"bill": _bill,"billid": _billid,"typeid": _typeid,"orgid": _orgid}).fetchone()
        
#############---表名,表ID,动作ID,审核信息,用户ID
    @classmethod
    def workflowcheck(cls,_bill,_billid,_billtype,_next_type,_message,_userid,_billdate,_orgunitid,_org_id,_checkuserids,_warnsealuser):
        strsql="""select public.workflowcheck(:bill,:billid,:billtype,:next_type,:message,:userid,:billdate,:orgunitid,:checkuserids,:warnsealuser,:org_id) """
        return db.session.execute(strsql,{"bill": _bill,"billid": _billid,"billtype": _billtype,"next_type": _next_type,"message": _message,"userid": _userid,"billdate": _billdate,"orgunitid": _orgunitid,"checkuserids": _checkuserids,"warnsealuser":_warnsealuser,"org_id": _org_id}).fetchone()

#############---表名,表ID,动作ID,审核信息,用户ID
    @classmethod
    def workflowuser(cls,_bill,_billid,_billtype,_orgunitid,_org_id):
        strsql="""select public.workflowuser(:bill,:billid,:billtype,:orgunitid,:org_id) """
        return db.session.execute(strsql,{"bill": _bill,"billid": _billid,"billtype": _billtype,"orgunitid": _orgunitid,"org_id": _org_id}).fetchone()
#############-检查是否可编辑--表名,表ID,用户ID
    @classmethod
    def workflowedit(cls,_bill,_billtype,_billid,_userid,_org_id):
        strsql="""select public.workflowedit(:bill,:billtype,:billid,:userid,:org_id) """
        return db.session.execute(strsql,{"bill": _bill,"billtype": _billtype,"billid": _billid,"userid": _userid,"org_id": _org_id}).fetchone()

#############-查询审批信息--表名,表ID
    @classmethod
    def workflowmessage(cls,_bill,_billid,_bill_type,_orgunitid):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (
            (select to_char(t1.updated_at, 'YYYY-MM-DD') as updated_at,
            COALESCE(t2.name,'') || ' ' || case when t1.status=3 then '已驳回' else t3.name end || ' ' || (case when t3.docstatus=0 or t3.docstatus=2 then '' when t1.status=0 then COALESCE(t4.checkname,'') ||'  待审核' when t1.status=1 then '取消审核' when t1.status=3 then '已驳回'  else '已审核' end) as message,
            (case when t1.status=0 then 'blue' when t1.status=1 or t1.status=3 then 'red' else 'green' end) as colors,t1.status,t1.comments
            From  public.workflow_action t1  
            left join users t2 on t1.completed_by=t2.id
            left join public.workflow_state t3 on t1.workflow_stateid=t3.id
            left join(select billid,bill_enum,COALESCE(string_agg(t2.name,','),'') as checkname
			From public.workflow_bill_state t1 , public.users t2  
			where t1.billid=:billid and t1.bill_enum=:bill and t1.is_active=true and
			(t1.allow_group && t2.groups or t2.id=any(t1.allow_user)) group by billid,bill_enum) t4 on t1.billid=t4.billid and t1.bill_enum=t4.bill_enum
            where t1.workflow_bill_stateid in(select COALESCE(t1.id,0)
			from public.workflow_bill_state t1 inner join public.workflow_state t2 on t1.workflow_stateid=t2.id
            inner join public.workflow_action t3 on t1.id=t3.workflow_bill_stateid
			 where t1.bill_enum=:bill and t1.billid=:billid and (t1.is_active=true or t2.docstatus=2 or t3.status=3)) 
            order by t1.workflow_bill_stateid,t1.created_at,t1.id)
           union all
            (WITH RECURSIVE cte AS (
            SELECT A.workflow_stateid,next_workflow_stateid,CAST ('A' || A.workflow_stateid AS VARCHAR(4000)) AS name_full_path
            FROM public.workflow_transition A where A .workflowid=(select COALESCE(max(id),0)From public.workflow 
			where bill_enum=:bill and bill_type=:bill_type and is_active=true and (orgunitid=0 or orgunitid=:orgunitid))  and
            A.workflow_stateid in(select COALESCE(max(t1.workflow_stateid),(select id from  public.workflow_state where docstatus=0))
			from public.workflow_bill_state t1 inner join public.workflow_state t2 on t1.workflow_stateid=t2.id
			 where t1.bill_enum=:bill and t1.billid=:billid and (t1.is_active=true or t2.docstatus=2))
            and public.workflowtabsel(:bill,A.condition,'id','id',:billid) >0
            UNION ALL
                SELECT K.workflow_stateid,K.next_workflow_stateid,CAST(C.name_full_path || '/' || K.workflow_stateid AS VARCHAR (4000)) AS name_full_path
                FROM (select workflow_stateid,next_workflow_stateid From public.workflow_transition
                where workflowid=(select COALESCE(max(id),0)From public.workflow 
			where bill_enum=:bill and bill_type=:bill_type and is_active=true and (orgunitid=0 or orgunitid=:orgunitid))
                and public.workflowtabsel(:bill,condition,'id','id',:billid) >0) K
                INNER JOIN cte C ON C.next_workflow_stateid = K.workflow_stateid
            ) SELECT ' ' as updated_at,case when t3.docstatus=2 then t3.name else t3.name  || ' 待审核' end  as message,'gray' as colors,0 as status,'' as comments
            FROM  cte left join public.workflow_state t3 on cte.next_workflow_stateid=t3.id order by cte.name_full_path )
            ) t """
        return db.session.execute(strsql,{"bill": _bill,"billid": _billid,"bill_type":_bill_type,"orgunitid":_orgunitid}).fetchone()
###################################20200712
@generic_repr("id", "workflow_stateid", "workflowid")
class Workflow_bill_state(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="workflow_bill_state")
    userid = Column(db.Integer) #--操作员ID
    docstatus = Column(db.SmallInteger, default=0, nullable=True)
    workflowid = Column(db.Integer)
    workflow_stateid = Column(db.Integer)
    billid = Column(db.Integer)
    bill_enum = Column(db.String(140), default=None, nullable=True)
    bill_type = Column(db.SmallInteger, default=0, nullable=True)
    allow_user = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.Integer)), nullable=True
    )
    allow_group = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.Integer)), nullable=True
    )
    allow_col = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )
    is_active = Column(db.Boolean, default=True, nullable=True)
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项

    __tablename__ = "workflow_bill_state"
    __table_args__ = (db.Index("workflow_bill_state_org_id","org_id", "id", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.workflowid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "docstatus": self.docstatus,
            "workflowid": self.workflowid,
            "workflow_stateid": self.workflow_stateid,
            "billid": self.billid,
            "bill_enum": self.bill_enum,
            "bill_type": self.bill_type,
            "allow_user": self.allow_user,
            "allow_group": self.allow_group,
            "allow_col": self.allow_col,
            "message": self.message,
            "is_active": self.is_active,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }
    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    @classmethod
    def get_by_workflowid(cls,workflow_id):
        return cls.query.filter(cls.workflowid == workflow_id).first()
###################################20200712
@generic_repr("id", "workflow_stateid", "workflowid")
class Workflow_transition(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="workflow_transition")
    userid = Column(db.Integer) #--操作员ID
    workflowid = Column(db.Integer)
    workflow_stateid = Column(db.Integer)
    next_type = Column(db.Integer)
    next_workflow_stateid = Column(db.Integer)
    allow_user = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.Integer)), nullable=True
    )
    allow_group = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.Integer)), nullable=True
    )
    allow_col = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )
    condition = Column(db.Text, default=None, nullable=True)  #  纪录事项

    __tablename__ = "workflow_transition"
    __table_args__ = (db.Index("workflow_transition_org_workflowid_workflow_stateid","org_id", "workflowid","workflow_stateid", "next_type", "next_workflow_stateid", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.workflowid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "workflowid": self.workflowid,
            "workflow_stateid": self.workflow_stateid,
            "next_type": self.next_type,
            "next_workflow_stateid": self.next_workflow_stateid,
            "allow_user": self.allow_user,
            "allow_group": self.allow_group,
            "allow_col": self.allow_col,
            "condition": self.condition,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    @classmethod
    def get_by_workflowid(cls,workflow_id):
        return cls.query.filter(cls.workflowid == workflow_id).first()
###################################20200712
@generic_repr("id", "name")
class Workflow_state(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="workflow_state")
    userid = Column(db.Integer) #--操作员ID
    docstatus = Column(db.SmallInteger, default=0, nullable=True)#---0开始，1 中间审批流程，2审批结束
    name = Column(db.String(255)) #名称

    __tablename__ = "workflow_state"
    __table_args__ = (db.Index("workflow_state_org_name","org_id", "name", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "docstatus": self.docstatus,
            "name": self.name,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()


###################################20201227
@generic_repr("code", "name")
class Workflow_action(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="workflow_action")
    userid = Column(db.Integer)
    workflowid = Column(db.Integer)
    billid = Column(db.Integer)
    bill_enum = Column(db.String(255))#表名
    bill_type = Column(db.Integer ,default=0, nullable=True)
    workflow_bill_stateid = Column(db.Integer ,default=0)
    workflow_stateid = Column(db.Integer)
    completed_by = Column(db.Integer)
    status = Column(db.Integer ,default=0)
    comments = Column(db.Text, default=None, nullable=True)

    __tablename__ = "workflow_action"

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "org_id": self.org_id,
            "userid": self.userid,
            "workflowid": self.workflowid,
            "billid": self.billid,
            "bill_enum": self.bill_enum,
            "bill_type": self.bill_type,
            "workflow_bill_stateid": self.workflow_bill_stateid,
            "workflow_stateid": self.workflow_stateid,
            "completed_by": self.completed_by,
            "status": self.status,
            "comments": self.comments,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

###################################20200715
@generic_repr("id","numhead","numdate","code", "name","numfoot","footlenth")
class Tabletype(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="tabletype")
    code = Column(db.String(255))#表名
    type = Column(db.Integer, default=0) #--默认类型
    name = Column(db.String(255)) #标题
    numauto = Column(db.Boolean, default=True, nullable=True) #是否自动编号
    numhand = Column(db.Boolean, default=False, nullable=True) #是否手动编号
    numhead = Column(db.String(255), default=None, nullable=True)#标志
    numdate = Column(db.String(255), default=None, nullable=True) #--按日期YYYYMMDD
    numfoot = Column(db.Integer, default=1, nullable=True) #--流水号默认从1开始
    footlenth = Column(db.Integer, default=4, nullable=True) #--流水号长度

    __tablename__ = "tabletype"
    __table_args__ = (db.Index("tabletype_org_code_type_name","org_id","code", "type", "name", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "type": self.type,
            "name": self.name,
            "numauto": self.numauto,
            "numhand": self.numhand,
            "numhead": self.numhead,
            "numdate": self.numdate,
            "numfoot": self.numfoot,
            "footlenth": self.footlenth,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('tabletype_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

###################################20201227
@generic_repr("code", "name")
class Tablefield(db.Model):
    id = Column(db.Integer, primary_key=True)
    tablename = Column(db.String(255))#表名
    rownum = Column(db.Integer, default=1, nullable=True) #--流水号默认从1开始
    code = Column(db.String(255)) #标题
    name = Column(db.String(255)) #是否自动编号
    is_activity = Column(db.Integer,default=1) #--流水号默认从1开始

    __tablename__ = "tablefield"
    __table_args__ = (db.Index("tablefield_code_tablename","code", "tablename", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "tablename": self.tablename,
            "rownum": self.rownum,
            "code": self.code,
            "name": self.name,
            "is_activity": self.is_activity,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
